今天要介紹的是如何利用C#與SQL的遞迴查詢找出Aras CAD的多階父子階，因為Aras的CAD都是利用CAD_Structure來關聯出CAD的之間的父子階關係，其中CAD之間是利用Related_id與Source_id來做串表，因此我們可以利用遞迴的方式來達成功能

C#

1. 首先要介紹的是利用C#的遞迴來查詢父子階，在Method中利用CAD_Structure的source_id與related_id的關係在利用IOM來查詢出其父子階，之後在Method中撰寫遞迴並利用遞迴的方式重複呼叫自己(Method)，藉此不斷的遞迴查詢某個子階父階的父階...，最後在利用Tuple的資料結構return多個回傳值回去，達成多階父子階查詢的功能
   

public class ParentSearch
{
    public List<string> CAD_Structure_MultipleSonID = new List<string>();
    public List<string> CAD_Structure_MultipleParentID = new List<string>();

    public Tuple<List<string>, List<string>> Multiple_Search(string sonID, 
    string select_url,string select_user_name, string select_user_password, 
    string select_db_name)
    {
        Connection con = new Connection();
        Innovator inn = con.Connect(select_url, select_user_name, 
                                    select_user_password,select_db_name);

        List<string> CAD_Structure_ParentID = new List<string>();
        List<string> null_List = new List<string>();

        Item CAD_Structure = inn.newItem("CAD Structure", "get");
        CAD_Structure.setAttribute("select", "id,source_id,related_id");
        CAD_Structure.setAttribute("where", "[CAD_Structure].related_id=
                                   '"+ sonID + "'");
        CAD_Structure = CAD_Structure.apply();

        for (int i = 0; i <= CAD_Structure.getItemCount() - 1; i++)
        {
            Item CAD_Structure_Single = CAD_Structure.getItemByIndex(i);
            CAD_Structure_ParentID.Add(CAD_Structure_Single.getProperty
                                       ("source_id", ""));
            CAD_Structure_MultipleSonID.Add(sonID);
            CAD_Structure_MultipleParentID.Add(CAD_Structure_ParentID[i]);
        }
        if (CAD_Structure.getItemCount() == 0)
        {
            return Tuple.Create<List<string>, List<string>>(null_List, 
                                                            null_List);
        }
        else
        {
            for (int i = 0; i <= CAD_Structure.getItemCount() - 1; i++)
            {
                Multiple_Search(CAD_Structure_ParentID[i], select_url, 
                select_user_name,select_user_password, select_db_name);
            }         
        }
        return Tuple.Create<List<string>, List<string>>
        (CAD_Structure_MultipleSonID,CAD_Structure_MultipleParentID);
    }
}

Controller

1. 接下來是Controller的部分，首先先建立剛剛ParentSearch的Class物件，在利用Tuple的資料型態接收多個回傳值，這樣就能夠完成取得多階父子階的功能了，若想了解Tuple用法的朋友可以看 (https://ithelp.ithome.com.tw/articles/10220548) 的介紹
   

List<string> CAD_Structure_MultipleSonID = new List<string>();
List<string> CAD_Structure_MultipleParentID = new List<string>();
List<int> RankList = new List<int>();

ParentSearch Parent = new ParentSearch();
Tuple<List<string>, List<string>> CAD_Structure_Multiple_tuple= 
Parent.Multiple_Search(sonID, select_url,select_user_name,  
select_user_password, select_db_name);

CAD_Structure_MultipleSonID = CAD_Structure_Multiple_tuple.Item1;
CAD_Structure_MultipleParentID = CAD_Structure_Multiple_tuple.Item2;

SQL

1. 最後要介紹的為SQL利用遞迴查詢的方式將CAD_Structure資料表中的多階的父子階關係查詢出來，再重新組成一個新的多階父子階CAD資料表並顯示出來，因為遞迴的方式都差不多，所以詳細的方式這邊就不在多做贅述

with [cad_new] as (
select cbom.SOURCE_ID as tr_sid,cbom.RELATED_ID as tr_rid,c.ITEM_NUMBER 
as parent,cr.ITEM_NUMBER as son ,cr.NAME,1 as level
from [innovator].[CAD_Structure] as cbom
inner join [innovator].[CAD] as c on cbom.SOURCE_ID=c.id
inner join [innovator].[CAD] as cr on cbom.RELATED_ID=cr.id
where cr.ITEM_NUMBER='1' and cr.IS_CURRENT='1'

UNION ALL

select cbom2.SOURCE_ID as tr_sid,cbom2.RELATED_ID as tr_rid,c.ITEM_NUMBER 
as parent,cr.ITEM_NUMBER as son,cr.NAME,level + 1
from [innovator].[CAD_Structure] as cbom2
inner join [cad_new] as bnew on bnew.tr_sid=cbom2.RELATED_ID
inner join [innovator].[CAD] as c on cbom2.SOURCE_ID =c.id
inner join [innovator].[CAD] as cr on cbom2.RELATED_ID=cr.id
)

select rank() OVER (ORDER by ta.tr_sid,ta.tr_rid ) as rank,* from [cad_new]
as ta
order by rank